﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Common;
using System.Data;
using Sinacor.Infra.Service.Data;
using Sinacor.Infra.Service.Security.Authorization;
using Sinacor.Infra.Service.Common;

namespace Sinacor.Servico.Bovespa.Corretagem.ResourceAccess
{
    //Task  : 5617 
    // Data : 11/03/2009
    // Autor:Apsilva
    /// <summary>
    /// Classe de Acesso a Base 
    /// </summary>
    public class Desconto: BaseResourceAccess
    {
        #region Construtor

        /// <summary>
        /// Construtor padrão
        /// </summary>
        public Desconto()
            : base(AuthorizationContext.Current.CompanyId)
        {
        }

        #endregion

        #region AjustarConflitoDesconto
        /// <summary>
        /// Método AjustarConflitoDesconto
        /// </summary>
        public void AjustarConflitoDesconto(BusinessEntity.Desconto DescontoConflito, DateTime DataAjuste)
        {
            StringBuilder sql = new StringBuilder();

            sql.Append(@"UPDATE TORDESCESP  SET DT_FIM = :DataAjuste  
							WHERE DT_INICIO = :DataInicial AND
							      DT_FIM = :DataFinal AND 
							      PC_NORMAL = :PercentualDescontoAcresimo AND
							      PC_ALLIN = :PercentualAllIn
                      ");

            using (DbCommand command = this.DataBase.GetSqlStringCommand(sql.ToString()))
            {
                this.DataBase.AddInParameter(command, "DataAjuste", DbType.DateTime, DataAjuste);

                if (DescontoConflito.Periodo.DataInicial != null)
                    this.DataBase.AddInParameter(command, "DataInicial", DbType.DateTime, DescontoConflito.Periodo.DataInicial);

                if (DescontoConflito.Periodo.DataFinal != null)
                    this.DataBase.AddInParameter(command, "DataFinal", DbType.DateTime,DescontoConflito.Periodo.DataFinal);

                this.DataBase.AddInParameter(command, "PercentualDescontoAcresimo", DbType.Decimal , DescontoConflito.PercentualDescontoAcresimo);
                this.DataBase.AddInParameter(command, "PercentualAllIn", DbType.Decimal, DescontoConflito.PercentualAllIn);
                this.DataBase.ExecuteNonQuery(command);

            }
        }

        #endregion


        #region ConsultarConflitoPeriodo
        /// <summary>
        /// Método ConsultarConflitoPeriodo
        /// </summary>
        public List<BusinessEntity.Desconto> ConsultarConflitoPeriodo(BusinessEntity.Desconto request)
        {
            List<BusinessEntity.Desconto> response = null;
            StringBuilder sql = new StringBuilder();

            sql.Append(@"SELECT DT_INICIO DataInicial, DT_FIM DataFinal, PC_NORMAL PercentualDescontoAcresimo,PC_ALLIN PercentualAllIn  FROM TORDESCESP ");
            sql.Append(@"				WHERE (:DataInicio BETWEEN DT_INICIO AND DT_FIM)");
            sql.Append(@"                OR (:DataFinal BETWEEN DT_INICIO AND DT_FIM)");
            sql.Append(@"                OR (DT_INICIO BETWEEN :DataInicio AND :DataFinal)");
            sql.Append(@"                OR (DT_FIM BETWEEN :DataInicio AND :DataFinal)");
			sql.Append(@"				ORDER BY DT_INICIO ASC");

            using (DbCommand command = this.DataBase.GetSqlStringCommand(sql.ToString()))
            {
              
                this.DataBase.AddInParameter(command, "DataInicio", DbType.DateTime, request.Periodo.DataInicial);
                this.DataBase.AddInParameter(command, "DataFinal", DbType.DateTime, request.Periodo.DataFinal);
              
                response = this.FindAll<BusinessEntity.Desconto>(command, new ConstructEntity<BusinessEntity.Desconto>(ConstrutorDesconto));
            }

            return response;
        }

        #endregion


        #region ConstrutorConstrutorDesconto
        protected BusinessEntity.Desconto ConstrutorDesconto(IDataReader reader)
        {
            BusinessEntity.Desconto entity = new BusinessEntity.Desconto();

            entity.PercentualAllIn = reader.GetValue<decimal?>("PercentualAllIn");
            entity.PercentualDescontoAcresimo = reader.GetValue<decimal?>("PercentualDescontoAcresimo");
            entity.Periodo = new Sinacor.Servico.Bovespa.Corretagem.BusinessEntity.Periodo();
            entity.Periodo.DataInicial = reader.GetValue<DateTime?>("DataInicial");
            entity.Periodo.DataFinal = reader.GetValue<DateTime?>("DataFinal");
            return entity;
        }
        #endregion


        #region ConsultarDescontos
        /// <summary>
        /// Método ConsultarDescontos
        /// </summary>
        public List<BusinessEntity.Desconto> ConsultarDescontos(BusinessEntity.Parameter.FiltroDescontoChave request)
        {
            List<BusinessEntity.Desconto> response = null;
            StringBuilder sql = new StringBuilder();

            sql.Append(@"SELECT DT_INICIO DataInicial, DT_FIM DataFinal, PC_NORMAL PercentualDescontoAcresimo,PC_ALLIN PercentualAllIn FROM TORDESCESP");

            String where = "Where  ";
            if (request.Periodo != null)
            {
                if (request.Periodo.DataInicial != null)
                {
                    sql.Append(where);
                    sql.Append("   DT_INICIO >= :DataInicial AND");
                    where = "";
                }


                if (request.Periodo.DataFinal != null)
                {
                    sql.Append(where);

                    sql.Append("  DT_FIM <= :DataFinal AND");

                    where = "";
                }

            }
            if (request.PercentualDescontoAcresimo != null)
            {
                sql.Append(where); 
                sql.Append("  PC_NORMAL = :PercentualDescontoAcresimo AND");
                where = ""; 
            }
             

            if (request.PercentualAllIn != null)
            {
                sql.Append(where); 
                sql.Append("  PC_ALLIN = :PercentualAllIn AND");
                where = ""; 
            }
            
            sql.Replace("AND", "", sql.Length - 4, 3);


            sql.Append(" ORDER BY DT_INICIO ASC "); 

            using (DbCommand command = this.DataBase.GetSqlStringCommand(sql.ToString()))
            {
                
                if (request.Periodo.DataInicial != null) 
                    this.DataBase.AddInParameter(command, "DataInicial", DbType.DateTime, request.Periodo.DataInicial);

                if (request.Periodo.DataFinal != null) 
                    this.DataBase.AddInParameter(command, "DataFinal", DbType.DateTime, request.Periodo.DataFinal);
        
                if (request.PercentualDescontoAcresimo !=null) 
                    this.DataBase.AddInParameter(command, "PercentualDescontoAcresimo", DbType.Decimal, request.PercentualDescontoAcresimo);
                
                if (request.PercentualAllIn !=null)
                    this.DataBase.AddInParameter(command, "PercentualAllIn", DbType.Decimal, request.PercentualDescontoAcresimo);

                response = this.FindAll<BusinessEntity.Desconto>(command, new ConstructEntity<BusinessEntity.Desconto>(ConstrutorDesconto));
            }

            return response;
        }

        #endregion


        #region ExcluirDesconto
        /// <summary>
        /// Método ExcluirDesconto
        /// </summary>
        public void ExcluirDesconto(BusinessEntity.Parameter.FiltroDescontoChave request)
        {
            StringBuilder sql = new StringBuilder();

            sql.Append(@"DELETE FROM TORDESCESP WHERE DT_INICIO = :DataInicial AND  DT_FIM = :DataFinal AND PC_NORMAL = :PercentualDescontoAcresimo AND PC_ALLIN = :PercentualAllIn ");

            using (DbCommand command = this.DataBase.GetSqlStringCommand(sql.ToString()))
            {
                if (request.Periodo.DataInicial != null)
                    this.DataBase.AddInParameter(command, "DataInicial", DbType.DateTime, request.Periodo.DataInicial);

                if (request.Periodo.DataFinal != null)
                    this.DataBase.AddInParameter(command, "DataFinal", DbType.DateTime, request.Periodo.DataFinal);

                if (request.PercentualDescontoAcresimo !=null) 
                this.DataBase.AddInParameter(command, "PercentualDescontoAcresimo", DbType.Decimal, request.PercentualDescontoAcresimo);

                if (request.PercentualAllIn !=null)
                    this.DataBase.AddInParameter(command, "PercentualAllIn", DbType.Decimal, request.PercentualAllIn);
                this.DataBase.ExecuteNonQuery(command);

            }
        }

        #endregion


        #region IncluirDesconto
        /// <summary>
        /// Método IncluirDesconto
        /// </summary>
        public void IncluirDesconto(BusinessEntity.Desconto request)
        {
            StringBuilder sql = new StringBuilder();

            sql.Append(@"INSERT INTO TORDESCESP (DT_INICIO, DT_FIM, PC_NORMAL, PC_ALLIN) VALUES(:DataInicial, :DataFinal, :PercentualDescontoAcresimo, :PercentualAllIn)
                      ");

            using (DbCommand command = this.DataBase.GetSqlStringCommand(sql.ToString()))
            {
                if (request.Periodo.DataInicial != null)
                    this.DataBase.AddInParameter(command, "DataInicial", DbType.DateTime, request.Periodo.DataInicial);

                if (request.Periodo.DataFinal != null)
                    this.DataBase.AddInParameter(command, "DataFinal", DbType.DateTime, request.Periodo.DataFinal);

                if (request.PercentualDescontoAcresimo !=null) 
                    this.DataBase.AddInParameter(command, "PercentualDescontoAcresimo", DbType.Decimal, request.PercentualDescontoAcresimo);

                if (request.PercentualAllIn !=null) 
                    this.DataBase.AddInParameter(command, "PercentualAllIn", DbType.Decimal, request.PercentualAllIn);
                this.DataBase.ExecuteNonQuery(command);

            }
        }



        #endregion


        #region ObterDesconto
        /// <summary>
        /// Método ObterDesconto
        /// </summary>
        public BusinessEntity.Desconto ObterDesconto(BusinessEntity.Parameter.FiltroDesconto request)
        {
            BusinessEntity.Desconto response = null;
            StringBuilder sql = new StringBuilder();

            sql.Append(@"SELECT DT_INICIO DataInicial, DT_FIM DataFinal,	PC_NORMAL PercentualDescontoAcresimo,PC_ALLIN PercentualAllIn FROM TORDESCESP WHERE :Data BETWEEN DT_INICIO AND DT_FIM");

            using (DbCommand command = this.DataBase.GetSqlStringCommand(sql.ToString()))
            {
               
                this.DataBase.AddInParameter(command, "Data", DbType.Int32, request.Data);
                response = this.FindOne<BusinessEntity.Desconto>(command, new ConstructEntity<BusinessEntity.Desconto>(ConstrutorDesconto));
            }

            return response;
        }
        #endregion


        #region ConsultarDescontosPeriodo
        public List<BusinessEntity.Desconto> ConsultarDescontosPeriodo(DateTime DataInicial) 
        {

            List<BusinessEntity.Desconto> response = null;
            StringBuilder sql = new StringBuilder();

            sql.Append(@"            SELECT DT_INICIO DataInicial, 
       DT_FIM DataFinal, 
       PC_NORMAL PercentualDescontoAcresimo, 
       PC_ALLIN PercentualAllIn 
FROM TORDESCESP
WHERE DT_INICIO = :DataInicial
ORDER BY DT_INICIO ASC");

            using (DbCommand command = this.DataBase.GetSqlStringCommand(sql.ToString()))
            {

                this.DataBase.AddInParameter(command, "DataInicial", DbType.DateTime, DataInicial);
                response = this.FindAll<BusinessEntity.Desconto>(command, new ConstructEntity<BusinessEntity.Desconto>(ConstrutorDesconto));
            }

            return response;


        }
        #endregion

    }
}
